




IF EXISTS (SELECT name FROM sysobjects 
	WHERE name = 'ZDC_PROC_CPT_ERROR_DATA' and type = 'P')
DROP PROCEDURE ZDC_PROC_CPT_ERROR_DATA
GO
-----------------CREA PROC ZDC_PROC_CPT_ERROR_DATA
CREATE  PROC ZDC_PROC_CPT_ERROR_DATA
 @DT_S DATE
,@DT_E DATE 
AS  BEGIN 

 ---- 组织 ----
 DECLARE @ORG BIGINT

 SELECT TOP 1 @ORG = ID
 FROM Base_Organization 
 WHERE CODE = '150'   -- 150 岱星


  --------------------------------------

  DECLARE @RID BIGINT -- 收货单ID
  DECLARE @MO BIGINT -- 生产订单ID 
  DECLARE @ITEM_P BIGINT -- 产品ID
  DECLARE @ITEM_P_CD NVARCHAR(100) -- 产品CODE 
  DECLARE @ITEM_P_CD_SAP NVARCHAR(100) -- 产品CODE 
  DECLARE @PROJECT BIGINT -- 项目ID
  DECLARE @PRJ_CD NVARCHAR(100) -- 项目号

  
  DECLARE @PRJ_CD_D NVARCHAR(100) -- 项目号
  DECLARE @ITEM_P_CD_D NVARCHAR(100) -- 产品CODE
  DECLARE @ITEM_C_CD_D_U9 NVARCHAR(100) -- 材料CODE
  DECLARE @ITEM_C_CD_D_SAP NVARCHAR(100) -- 材料CODE
  DECLARE @ITEM_T_CD NVARCHAR(100) -- 替代料CODE
  DECLARE @ISUQTY NUMERIC(16,6) -- 数量
  DECLARE @ISUQTY_D NUMERIC(16,6) -- 数量


  DECLARE @N INT 
  DECLARE @RCOUNT INT 

  DECLARE @N_D INT 
  DECLARE @RCOUNT_D INT 

  DECLARE @MO_DOCNO NVARCHAR(100) -- 生产订单号
  
  DECLARE @IsSucess NVARCHAR(10)  -- 校验状态 ： 1 成功 0 失败
  DECLARE @ErrorMsg NVARCHAR(2000) 
  DECLARE @ISSAP NVARCHAR(10)  -- 是否SAP单据 ： 1 是 0 否

  DECLARE @TRANSID BIGINT 
  SET @TRANSID = 0

  SELECT  @IsSucess = '1', @ErrorMsg = '', @ISSAP = '0' , @RID = 0 , @ITEM_P_CD = '', @ITEM_P_CD_SAP = '' 



  --------------------------
  DELETE ZDC_SAP_CPT_ERROR

  ---- 获取 东软当天入库的生产订单

  If object_id('tempdb..#CPTMO') is not null
	Drop Table #CPTMO


  SELECT  ROW_NUMBER () OVER (ORDER BY MX.PROJECT,MX.ITEM_P ) AS SQ 
         ,MX.PROJECT AS PROJECT
		 ,MX.PRJ_CD AS PRJ_CD
		 ,MX.ITEM_P AS ITEM_P
		 ,MX.ITEM_P_CD AS ITEM_P_CD
		 ,MX.ITEM_P_CD_SAP AS ITEM_P_CD_SAP
  INTO #CPTMO
  FROM ( SELECT  DISTINCT  ISNULL(RL.Project,0)  AS PROJECT 
                         , ISNULL(P.CODE,'') AS PRJ_CD
						 , MO.ITEMMASTER AS ITEM_P
						 , I.CODE AS ITEM_P_CD
						 , CASE WHEN LEN(ISNULL(I.CODE1,'')) <=2  THEN '' 
		                   ELSE SUBSTRING(ISNULL(I.CODE1,''),3,LEN(ISNULL(I.CODE1,'')) - 2)
			               END AS ITEM_P_CD_SAP
         FROM Complete_RcvRptDoc R 
         LEFT JOIN Complete_RcvRptDocLine RL ON R.ID = RL.RcvRptDoc
         LEFT JOIN MO_MO MO ON MO.ID = RL.MO
		 LEFT JOIN CBO_PROJECT P ON P.ID = ISNULL(RL.Project,0) 
         LEFT JOIN CBO_Department DP ON DP.ID = MO.Department
		 LEFT JOIN CBO_ItemMaster I ON I.ID = MO.ItemMaster
         WHERE R.DocState IN ( 1, 2 )
           AND CAST(R.ActualRcvTime AS DATE) BETWEEN @DT_S AND @DT_E
	       AND R.Org = @ORG 
		   AND ISNULL(RL.Project,0)  > 0 
		   --AND DP.CODE IN ('61103','61003') 
	       AND DP.CODE IN ('61103') 

		  ) MX
   
   
   SELECT @N = 1 , @RCOUNT = COUNT(*) FROM #CPTMO
   

   
   WHILE @N <= @RCOUNT 
   BEGIN

     SELECT TOP 1  @PROJECT = ISNULL(PROJECT ,0)
	            ,  @PRJ_CD  = PRJ_CD
				,  @ITEM_P = ISNULL(ITEM_P,0)
				,  @ITEM_P_CD = ISNULL(ITEM_P_CD,'')
				,  @ITEM_P_CD_SAP = ISNULL(ITEM_P_CD_SAP,'')
	            ,  @IsSucess = '1'  -- 每笔MO状态初始化
				,  @ErrorMsg = ''   -- 每笔MO错误信息清空
	 FROM #CPTMO
	 WHERE SQ = @N 

     
	 
	 DELETE  ZDC_BOM_ADDUP  --   WHERE  ITEM_ROOT = @ITEM_P

	 EXEC ZDC_PROC_BOM_ADDUP_END   @ORG ,@ITEM_P,1,0,@ITEM_P,'',1



	   -------- 写入 BOM中间表

	   INSERT ZDC_SAP_CPT_ERROR ( TRANSID ,  CREATEON,ORG_CD_U9
	                       ,DOCNO_U9,DOCNO_SAP
	                       ,ITEMCODE_P_U9 ,ITEMCODE_P_SAP ,RCVQTY 
	                       ,ITEMCODE_C_U9,ITEMCODE_C_SAP
						   ,ITEMCODE_T1_U9,ITEMCODE_T1_SAP
						   ,ITEMCODE_T2_U9,ITEMCODE_T2_SAP 
						   ,ISUQTY
						   ,IsSucess
	                        ) 
       
       SELECT         @TRANSID , GETDATE(), G.CODE 
	                , ISNULL(P.CODE ,'') , ISNULL(P.CODE ,'')
	                , @ITEM_P_CD

					--, CASE WHEN LEN(ISNULL(I.CODE1,'')) <=2  THEN '' 
		   --                ELSE SUBSTRING(ISNULL(I.CODE1,''),3,LEN(ISNULL(I.CODE1,'')) - 2)
			  --             END 
					, @ITEM_P_CD_SAP

					, ISNULL(M.RCVQTY,0)

					, ISNULL(MX.ITEMCODE_C_U9,'') , ISNULL(MX.ITEMCODE_C_SAP,'') 
		            , ISNULL(MX.ITEMCODE_T1_U9,'') , ISNULL(MX.ITEMCODE_T1_SAP,'') 
			        , ISNULL(MX.ITEMCODE_T2_U9,'') , ISNULL(MX.ITEMCODE_T2_SAP,'') 
			        , ISNULL(MX.ISUQTY,0) 
					, ''

       FROM (  SELECT  ISNULL(MO.PROJECT,0) AS Project 
	               , MO.ItemMaster AS ITEMID 
                   , SUM(ISNULL(MO.TotalEligibleRcvQty,0)) AS RCVQTY
              FROM MO_MO MO
	          LEFT JOIN CBO_Department DP ON DP.ID = MO.Department  
	          LEFT JOIN CBO_Project P ON P.ID = ISNULL(MO.Project ,0) 
	          WHERE ISNULL(MO.Project,0) = @PROJECT 
			    AND DP.CODE = '61103'
			  GROUP BY ISNULL(MO.PROJECT,0) , MO.ItemMaster

	            --AND  ISNULL(MO.Project,0) > 0    --------------------------------------------------  切换本地环境

              ) M 

       LEFT JOIN (  
	   ------- BOM -------
	        SELECT  Z.ITEM_ROOT AS ITEM_P
                  , IC.CODE AS ITEMCODE_C_U9  
				  , ISNULL(IC.CODE1,'') AS ITEMCODE_C_SAP
                  , '' AS ITEMCODE_T1_U9, '' AS ITEMCODE_T1_SAP
				  , '' AS ITEMCODE_T2_U9, '' AS ITEMCODE_T2_SAP

				  --, SUM( ISNULL(Z.USAGEQTY_TOT_ROOT,0)) AS ISUQTY
				  , 0 AS ISUQTY

			FROM ZDC_BOM_ADDUP Z
			LEFT JOIN CBO_ItemMaster IC ON IC.ID = Z.ITEM_C
			WHERE Z.ITEM_ROOT = @ITEM_P
	          AND ISNULL(Z.END_FG,0) = 1
			  AND Z.USAGEQTY_TOT_ROOT > 0 
			  AND IC.CODE NOT LIKE '%.Y'    -- 东软智行客供料 U9料号

	        GROUP BY Z.ITEM_ROOT , IC.CODE  , ISNULL(IC.CODE1,'')

            ) MX ON MX.ITEM_P = M.ITEMID

       LEFT JOIN CBO_Project P ON P.ID = ISNULL(M.Project ,0) 
       LEFT JOIN CBO_ItemMaster I ON I.ID = ISNULL(M.ITEMID,0)
       LEFT JOIN Base_Organization G ON G.ID = I.Org 

	   WHERE ISNULL(MX.ITEMCODE_C_U9,'') <> ''


	   ---- 无替代料消耗量


       UPDATE Z
       SET ISUQTY = ISNULL(MP.ISUQTY,0)
       FROM ZDC_SAP_CPT_ERROR Z 
       LEFT JOIN (  SELECT  P.CODE AS Project
                  , IC.CODE AS ITEMCODE_C_U9  , ISNULL(IC.CODE1,'') AS ITEMCODE_C_SAP
                  , '' AS ITEMCODE_T1_U9, '' AS ITEMCODE_T1_SAP
				  , '' AS ITEMCODE_T2_U9, '' AS ITEMCODE_T2_SAP
				  , SUM( ISNULL(MP.IssuedQty,0)) AS ISUQTY 
            FROM MO_MOPickList MP 
	        LEFT JOIN MO_MO MO ON MO.ID = MP.MO
	        LEFT JOIN CBO_ItemMaster IC ON IC.ID = MP.ItemMaster 
			LEFT JOIN CBO_PROJECT P ON P.ID = MO.Project
	        --LEFT JOIN CBO_ItemMaster I1 ON I1.ORG = MO.ORG AND I1.CODE = ISNULL(MP.DescFlexField_PrivateDescSeg5,'')
	        --LEFT JOIN CBO_ItemMaster I2 ON I2.ORG = MO.ORG AND I2.CODE = ISNULL(MP.DescFlexField_PrivateDescSeg6,'')
			WHERE P.CODE = @PRJ_CD
	          AND  ISNULL(MO.Project ,0)  > 0 
		      AND MP.IssuedQty > 0 
		      AND IC.CODE LIKE '2019101%'   -- 东软智行客供料 U9料号
		      AND IC.CODE NOT LIKE '%.Y'    -- 东软智行客供料 U9料号
			  AND ISNULL(MP.DescFlexField_PrivateDescSeg5,'') = ''
			GROUP BY  P.CODE, IC.CODE  , ISNULL(IC.CODE1,'')
              ) MP ON Z.DOCNO_U9 = MP.Project 
			      AND MP.ITEMCODE_C_U9 = Z.ITEMCODE_C_U9

       WHERE Z.DOCNO_U9 = @PRJ_CD



	   ---- 替代料1、替代料2 消耗量  --  SELECT * FROM ZDC_SAP_CPT_ERROR ORDER BY TRANSID , DOCNO_U9 , ITEMCODE_P_U9 , ITEMCODE_C_U9

	  
	  If object_id('tempdb..#TD') is not null
	    Drop Table #TD


	  SELECT   ROW_NUMBER () OVER (ORDER BY P.CODE , ISNULL(IC.CODE1,'') ,ISNULL(MP.DescFlexField_PrivateDescSeg5,'')  ) AS SQ
	              ,P.CODE AS PJT_CD
                  , IC.CODE AS ITEMCODE_C_U9  
				  , ISNULL(IC.CODE1,'') AS ITEMCODE_C_SAP
				  , ISNULL(MP.DescFlexField_PrivateDescSeg5,'')  AS ITEMCODE_T_U9
				  , SUM( ISNULL(MP.IssuedQty,0)) AS ISUQTY 
	  INTO  #TD
            FROM MO_MOPickList MP 
	        LEFT JOIN MO_MO MO ON MO.ID = MP.MO
	        LEFT JOIN CBO_ItemMaster IC ON IC.ID = MP.ItemMaster 
			LEFT JOIN CBO_PROJECT P ON P.ID = MO.Project
			WHERE P.CODE = @PRJ_CD
	          AND  ISNULL(MO.Project ,0)  > 0 
		      AND MP.IssuedQty > 0 
		      AND IC.CODE LIKE '2019101%'   -- 东软智行客供料 U9料号
		      AND IC.CODE NOT LIKE '%.Y'    -- 东软智行客供料 U9料号
			  AND ISNULL(MP.DescFlexField_PrivateDescSeg5,'') <> ''
			GROUP BY  P.CODE  , IC.CODE, ISNULL(IC.CODE1,'') , ISNULL(MP.DescFlexField_PrivateDescSeg5,'') 

	   SELECT @N_D = 1 , @RCOUNT_D = COUNT(*) 
	   FROM #TD


	   WHILE @N_D <= @RCOUNT_D 
	   BEGIN
	     
		 SELECT TOP 1 @PRJ_CD_D = PJT_CD
		             ,@ITEM_C_CD_D_U9 = ITEMCODE_C_U9
		             ,@ITEM_C_CD_D_SAP = ITEMCODE_C_SAP
					 ,@ITEM_T_CD = ITEMCODE_T_U9
					 ,@ISUQTY_D = ISUQTY
		 FROM #TD
		 WHERE SQ = @N_D


		 IF EXISTS ( SELECT Z.*
		             FROM ZDC_SAP_CPT_ERROR Z
					 WHERE Z.TRANSID = @TRANSID 
					   AND Z.DOCNO_U9 = @PRJ_CD_D
					   AND Z.ITEMCODE_C_U9 = @ITEM_T_CD
					   AND ISNULL(Z.ITEMCODE_T1_SAP ,'') = ''
					)
		 BEGIN


		   UPDATE Z
		   SET ITEMCODE_T1_SAP = @ITEM_C_CD_D_SAP 
		      ,ITEMCODE_T1_U9 = @ITEM_C_CD_D_U9
			  ,ISUQTY = ISUQTY + @ISUQTY_D
		   FROM ZDC_SAP_CPT_ERROR Z
		   WHERE Z.TRANSID = @TRANSID 
			 AND Z.DOCNO_U9 = @PRJ_CD_D
			 AND Z.ITEMCODE_C_U9 = @ITEM_T_CD


		 END 

		 ELSE IF EXISTS ( SELECT Z.*
		                  FROM ZDC_SAP_CPT_ERROR Z
					      WHERE Z.TRANSID = @TRANSID 
					        AND Z.DOCNO_U9 = @PRJ_CD_D
					        AND Z.ITEMCODE_C_U9 = @ITEM_T_CD
					        AND ISNULL(Z.ITEMCODE_T1_SAP ,'') <> ''
							AND ISNULL(Z.ITEMCODE_T2_SAP ,'') = ''
							)
		 BEGIN

		   UPDATE Z
		   SET ITEMCODE_T2_SAP = @ITEM_C_CD_D_SAP 
		      ,ITEMCODE_T2_U9 = @ITEM_C_CD_D_U9
			  ,ISUQTY = ISUQTY + @ISUQTY_D
		   FROM ZDC_SAP_CPT_ERROR Z
		   WHERE Z.TRANSID = @TRANSID 
			 AND Z.DOCNO_U9 = @PRJ_CD_D
			 AND Z.ITEMCODE_C_U9 = @ITEM_T_CD

		 END 

		 ELSE 
		 BEGIN

		   UPDATE Z
		   SET ISUQTY = ISUQTY + @ISUQTY_D
		   FROM ZDC_SAP_CPT_ERROR Z
		   WHERE Z.TRANSID = @TRANSID 
			 AND Z.DOCNO_U9 = @PRJ_CD_D
			 AND Z.ITEMCODE_C_U9 = @ITEM_T_CD

		 END 



	     SET @N_D = @N_D + 1 

	   END 


  
     SET @N = @N + 1 

   END 


   ----------  SELECT * FROM ZDC_SAP_CPT_ERROR

   SELECT  Z.DOCNO_U9 AS 项目号
         , Z.ITEMCODE_P_U9 AS U9成品料号
		 , Z.ITEMCODE_P_SAP AS SAP成品料号
		 , Z.RCVQTY AS 成品入库数
		 , Z.ITEMCODE_C_U9 AS U9原材料号
		 , ISNULL(I.NAME,'') AS U9原材料品名
		 , Z.ITEMCODE_C_SAP AS SAP原材料号
		 , Z.ITEMCODE_T1_U9 AS U9替代料号1
		 , Z.ITEMCODE_T1_SAP AS SAP替代料号1
		 , Z.ITEMCODE_T2_U9 AS U9替代料号2
		 , Z.ITEMCODE_T2_SAP AS SAP替代料号2
		 ,  ISNULL(Z.ISUQTY,0) AS 原材消耗数

   FROM ZDC_SAP_CPT_ERROR Z
   LEFT JOIN Base_Organization G ON G.CODE = Z.ORG_CD_U9
   LEFT JOIN CBO_ItemMaster I ON I.CODE = Z.ITEMCODE_C_U9 AND I.ORG = G.ID 
   
   WHERE ISNULL(Z.ISUQTY,0) = 0 
   
   ORDER BY Z.DOCNO_U9,Z.ITEMCODE_C_U9


END 


